﻿Imports System.Data
Imports System
Imports System.IO

Partial Class Content_Reportes_report_controls_ReporteMovimientos
    Inherits System.Web.UI.UserControl
    Dim ECConnString As String = ConfigurationManager.AppSettings("dbIP")
    Dim IControles As New Clases.controles
    Dim DIAS As String
    Dim MESS As String
    Dim YEARSS As String
    Dim valoranterior As Double



    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim q1, q2 As String
            q1 = "Select * from TDespachos where stat_id=1"
            IControles.fill_dropDownListBaseQuery(ddlDespacho, "TDespachos", "desp_id", "desp_nombre", q1)

            q2 = "Select * from TInstituciones where stat_id=1"
            IControles.fill_dropDownListBaseQuery(ddlInstitucion, "TInstituciones", "inst_id", "inst_nombre", q2)
            IControles.addNoneAllElement(ddlInstitucion)

            IControles.addNoneAllElement(ddlCliente)

            txtFechaI.Attributes.Add("onkpress", "return false")
            txtFechaI.Attributes.Add("onPaste", "return false")
            txtFechaF.Attributes.Add("onkeypress", "return false")
            txtFechaF.Attributes.Add("onPaste", "return false")

            hl_pdf.Visible = False
        End If


    End Sub

    Protected Sub ddlInstitucion_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlInstitucion.SelectedIndexChanged
        ddlCliente.Items.Clear()
        If ddlInstitucion.SelectedValue = "None" Then
            IControles.addNoneAllElement(ddlCliente)
        Else
            Dim q1 As String
            q1 = "Select * from vwClientes where desp_id='" & ddlDespacho.SelectedValue & "' and inst_id='" & ddlInstitucion.SelectedValue & "' order by NombreC"
            IControles.fill_dropDownListBaseQuery(ddlCliente, "vwClientes", "cl_id", "NombreContrato", q1)
            IControles.addNoneAllElement(ddlCliente)
        End If

    End Sub

    Protected Sub calculaFecha(ByVal dia As String, ByVal mes As String, ByVal years As String, ByVal febrero As String)
        Dim aux, auxi, auxil As Integer
        Dim mesN As String
        Dim diasArr(30), i As Integer
        diasArr(0) = 31
        diasArr(1) = 30
        diasArr(2) = 29
        diasArr(3) = 28
        diasArr(4) = 27
        diasArr(5) = 26
        diasArr(6) = 25
        diasArr(7) = 24
        diasArr(8) = 23
        diasArr(9) = 22
        diasArr(10) = 21
        diasArr(11) = 20
        diasArr(12) = 19
        diasArr(13) = 18
        diasArr(14) = 17
        diasArr(15) = 16
        diasArr(16) = 15
        diasArr(17) = 14
        diasArr(18) = 13
        diasArr(19) = 12
        diasArr(20) = 11
        diasArr(21) = 10
        diasArr(22) = 9
        diasArr(23) = 8
        diasArr(24) = 7
        diasArr(25) = 6
        diasArr(26) = 5
        diasArr(27) = 4
        diasArr(28) = 3
        diasArr(29) = 2
        diasArr(30) = 1

        aux = 50 - CType(dia, Integer)

        Select Case mes
            Case "1"
                If CType(dia, Integer) >= 1 And CType(dia, Integer) <= 20 Then
                    mesN = "11"
                ElseIf CType(dia, Integer) > 20 Then
                    mesN = "12"
                End If
                'mesN = "11"
            Case "2"
                If CType(dia, Integer) >= 1 And CType(dia, Integer) <= 20 Then
                    mesN = "12"
                ElseIf CType(dia, Integer) > 20 Then
                    mesN = "1"
                End If
                'mesN = "12"
            Case "3"
                If CType(dia, Integer) >= 1 And CType(dia, Integer) <= 20 Then
                    mesN = "1"
                ElseIf CType(dia, Integer) > 20 Then
                    mesN = "2"
                End If
                'mesN = "1"
            Case "4"
                If CType(dia, Integer) >= 1 And CType(dia, Integer) <= 20 Then
                    mesN = "2"
                ElseIf CType(dia, Integer) > 20 Then
                    mesN = "3"
                End If
                'mesN = "2"
            Case "5"
                If CType(dia, Integer) >= 1 And CType(dia, Integer) <= 20 Then
                    mesN = "3"
                ElseIf CType(dia, Integer) > 20 Then
                    mesN = "4"
                End If
                'mesN = "3"
            Case "6"
                If CType(dia, Integer) >= 1 And CType(dia, Integer) <= 20 Then
                    mesN = "4"
                ElseIf CType(dia, Integer) > 20 Then
                    mesN = "5"
                End If
                'mesN = "4"
            Case "7"
                If CType(dia, Integer) >= 1 And CType(dia, Integer) <= 20 Then
                    mesN = "5"
                ElseIf CType(dia, Integer) > 20 Then
                    mesN = "6"
                End If
                'mesN = "5"
            Case "8"
                If CType(dia, Integer) >= 1 And CType(dia, Integer) <= 20 Then
                    mesN = "6"
                ElseIf CType(dia, Integer) > 20 Then
                    mesN = "7"
                End If
                'mesN = "6"
            Case "9"
                If CType(dia, Integer) >= 1 And CType(dia, Integer) <= 20 Then
                    mesN = "7"
                ElseIf CType(dia, Integer) > 20 Then
                    mesN = "8"
                End If
                'mesN = "7"
            Case "10"
                If CType(dia, Integer) >= 1 And CType(dia, Integer) <= 20 Then
                    mesN = "8"
                ElseIf CType(dia, Integer) > 20 Then
                    mesN = "9"
                End If
                'mesN = "8"
            Case "11"
                If CType(dia, Integer) >= 1 And CType(dia, Integer) <= 20 Then
                    mesN = "9"
                ElseIf CType(dia, Integer) > 20 Then
                    mesN = "10"
                End If
                'mesN = "9"
            Case "12"
                If CType(dia, Integer) >= 1 And CType(dia, Integer) <= 20 Then
                    mesN = "10"
                ElseIf CType(dia, Integer) > 20 Then
                    mesN = "11"
                End If
                'mesN = "10"
        End Select

        Select Case mesN
            Case "1"
                If aux > 31 Then
                    auxi = aux - 31
                Else
                    auxi = 31 - aux
                End If
                'auxi = auxi - 1
            Case "2"
                If febrero = 28 Then
                    If aux > 28 Then
                        auxi = aux - 28
                    Else
                        auxi = 28 - aux
                    End If
                ElseIf febrero = 29 Then
                    If aux > 29 Then
                        auxi = aux - 29
                    Else
                        auxi = 29 - aux
                    End If
                End If
                'auxi = auxi - 1
            Case "3"
                If aux > 31 Then
                    auxi = aux - 31
                Else
                    auxi = 31 - aux
                End If
                'auxi = auxi - 1
            Case "4"
                If aux > 30 Then
                    auxi = aux - 30
                Else
                    auxi = 30 - aux
                End If
                'auxi = auxi - 1
            Case "5"
                If aux > 31 Then
                    auxi = aux - 31
                Else
                    auxi = 31 - aux
                End If
                'auxi = auxi - 1
            Case "6"
                If aux > 30 Then
                    auxi = aux - 30
                Else
                    auxi = 30 - aux
                End If
                'auxi = auxi - 1
            Case "7"
                If aux > 31 Then
                    auxi = aux - 31
                Else
                    auxi = 31 - aux
                End If
                'auxi = auxi - 1
            Case "8"
                If aux > 31 Then
                    auxi = aux - 31
                Else
                    auxi = 31 - aux
                End If
                'auxi = auxi - 1
            Case "9"
                If aux > 30 Then
                    auxi = aux - 30
                Else
                    auxi = 30 - aux
                End If
                'auxi = auxi - 1
            Case "10"
                If aux > 31 Then
                    auxi = aux - 31
                Else
                    auxi = 31 - aux
                End If
                'auxi = auxi - 1
            Case "11"
                If aux > 30 Then
                    auxi = aux - 30
                Else
                    auxi = 30 - aux
                End If
                'auxi = auxi - 1
            Case "12"
                If aux > 31 Then
                    auxi = aux - 31
                Else
                    auxi = 31 - aux
                End If
                'auxi = auxi - 1
        End Select

        auxi = auxi + 1
        DIAS = diasArr(auxi)
        MESS = mesN
        YEARSS = years
    End Sub

    Protected Sub ImageButton1_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ImageButton1.Click
        'hl_pdf.Visible = True
        'RangeValidator1.Type = ValidationDataType.Date
        'RangeValidator1.MinimumValue = CType(txtFechaI.Text, DateTime).ToString("dd-MM-yyyy")
        'RangeValidator1.MaximumValue = DateTime.Today.AddYears(100).ToString("dd-MM-yyyy")
        'RangeValidator1.ErrorMessage = "La Fecha Final debe ser mayor a la inicial"
        Dim fecha As Date
        Dim dia, mes, year, feb As String
        fecha = Now

        dia = fecha.Day
        mes = fecha.Month
        year = fecha.Year
        'MsgBox("dia: " & dia & ", mes: " & mes & ", año: " & year)
        If Date.IsLeapYear(fecha.Year) = True Then
            feb = 29
        Else
            feb = 28
        End If

        calculaFecha(dia, mes, year, feb)


        Dim qryBusq As String

        qryBusq = "Select movi_tipo_op,Cliente,contrato,despacho,inst_nombre,movi_fecha_op,movi_monto from vwMovimientos where 1=1"

        If txtFechaI.Text = "" And txtFechaF.Text = "" Then
            qryBusq = qryBusq & " and movi_fecha_op between '" & YEARSS & "-" & MESS & "-" & DIAS & "' and '" & year & "-" & mes & "-" & dia & "'"
        End If
        If ddlDespacho.SelectedValue <> "None" Then
            qryBusq = qryBusq & " and desp_id=" & ddlDespacho.SelectedValue & ""
        End If

        If ddlInstitucion.SelectedValue <> "None" Then
            qryBusq = qryBusq & " and inst_id=" & ddlInstitucion.SelectedValue & ""
        End If

        If ddlCliente.SelectedValue <> "None" Then
            qryBusq = qryBusq & " and cl_id=" & ddlCliente.SelectedValue & ""
        End If

        If txtFechaI.Text <> "" And txtFechaF.Text = "" Then
            qryBusq = qryBusq & " and movi_fecha_op between '" & txtFechaI.Text & "' and getdate()"
        End If

        If txtFechaI.Text = "" And txtFechaF.Text <> "" Then
            qryBusq = qryBusq & " and movi_fecha_op <= '" & txtFechaF.Text & "'"
        End If

        If txtFechaI.Text <> "" And txtFechaF.Text <> "" Then
            qryBusq = qryBusq & " and movi_fecha_op between '" & txtFechaI.Text & "' and '" & txtFechaF.Text & "'"
        End If


        'If (txtFechaI.Text <> "") And (txtFechaF.Text <> "") Then
        '    'qryBusq = qryBusq & " and movi_fecha_op between " & txtFechaI.Text & " and " & txtFechaF.Text & ""
        '    qryBusq = qryBusq & " and movi_fecha_op >= " & txtFechaI.Text & " and movi_fecha_op <= " & txtFechaF.Text & ""
        'End If
        ''If txtFechaOp.Text IsNot "" And txtFechaLiquid.Text IsNot "" Then
        ''    Query = Query & " AND [Fecha de Operación] >= '" & txtFechaOp.Text & "' AND [Fecha de Operación] <= '" & txtFechaLiquid.Text & "'"
        ''End If

        'If (txtFechaI.Text <> "") And (txtFechaF.Text = "") Then
        '    'qryBusq = qryBusq & " and movi_fecha_op between " & txtFechaI.Text & " and getdate()"
        '    qryBusq = qryBusq & " and movi_fecha_op >= " & txtFechaI.Text & " and movi_fecha_op <= and getdate()"
        'End If

        'If (txtFechaI.Text = "") And (txtFechaF.Text <> "") Then
        '    'qryBusq = qryBusq & " and movi_fecha_op between getdate() and " & txtFechaF.Text & ""
        '    qryBusq = qryBusq & " and movi_fecha_op <= " & txtFechaF.Text
        'End If

        Session("RepMov") = qryBusq
        IControles.fillGridViewPaging(gvMovimientos, qryBusq)
        btn_excel.Visible = True
    End Sub

    '***********************
    'Rutina exportar a Excel
    '***********************
    '
    Protected Sub exportToExcel(ByVal query As String)
        Dim gridView_toExport As New GridView
        IControles.fillGridViewPaging(gridView_toExport, Session("RepMov"))

        For i As Integer = 0 To gridView_toExport.Rows.Count - 1
            Dim row As GridViewRow = gridView_toExport.Rows(i)
        Next

        Dim sb As StringBuilder = New StringBuilder()
        Dim sw As IO.StringWriter = New IO.StringWriter(sb)
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
        Dim pagina As Page = New Page
        Dim form = New HtmlForm
        pagina.EnableEventValidation = False
        pagina.DesignerInitialize()
        pagina.Controls.Add(form)
        gridView_toExport.AllowPaging = False
        form.Controls.Add(gridView_toExport)
        pagina.RenderControl(htw)
        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"

        Response.AddHeader("Content-Disposition", "attachment;filename=mov" & Now.ToString("_dd_MMMM_yy_HH_mm_ss") & ".xls")
        Response.Charset = "UTF-8"
        Response.ContentEncoding = Encoding.Default
        Response.Write(sb.ToString())
        Response.End()

    End Sub

    'Protected Sub ExportToExcel1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ExportToExcel1.Click
    '    ExportToExcel1.ExportFileName = "ReporteMovimientos" & Now.ToString("_dd_MMMM_yy") & ".xls"
    'End Sub

    Protected Sub btn_excel_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btn_excel.Click
        'exportToExcel(gvMovimientos)
        exportToExcel(Session("RepMov"))
    End Sub
End Class
